﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Newtonsoft.Json;
using System.Reflection;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace CreateEntity
{
    public partial class Form1 : Form
    {
        private MySqlCommand cmd = null;
        private MySqlConnection conn = null;
        private SqlCommand cmdsql = null;
        private SqlConnection connsql = null;
        public static string connstring = "";
        public Form1()
        {
            InitializeComponent();
        }
        List<String> Tablenames = new List<String>();//建立一个列表，来存储数据库里面的所有表名
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                connstring = DataSourceText.Text;
                if (string.IsNullOrEmpty(connstring))
                {
                    MessageBox.Show("请输入数据库连接字符串");
                    return;
                }
                if (SqlType.SelectedIndex.ToString() == "1")
                {
                    MysqlShowTable();
                }
                else
                {
                    SqlServerShowTable();
                }
            }
            catch (Exception ex)
            {
                TableEntity.Text = ex.Message;
            }
        }
        /// <summary>
        /// 表名双击
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void TableName_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            int index = TableName.IndexFromPoint(e.Location);
            if (index != ListBox.NoMatches)

            {
                string selectTableName = TableName.SelectedItem.ToString();

                if (SqlType.SelectedIndex.ToString() == "1")
                {
                    GetMysqlTableStructure(selectTableName);
                }
                else
                {
                    GetSqlserverTableStructure(selectTableName);

                }
            }
            else
            {
                MessageBox.Show("请选择Table");
            }
        }
        #region Sqlserver 生成实体
        public void SqlServerShowTable()
        {
            try
            {
                connsql = new SqlConnection(connstring);
                connsql.Open();
                string queryTable = "select name from sys.tables";
                cmdsql = new SqlCommand(queryTable, connsql);
                using (SqlDataReader reader = cmdsql.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Tablenames.Add(reader.GetString(0));//将表名添加到定义好的列表中来
                        this.TableName.Items.Add(reader.GetString(0));
                    }
                }
                connsql.Close();
            }
            catch (Exception ex)
            {
                TableEntity.Text = ex.Message;
            }

        }
        /// <summary>
        /// 获取mysql表结构
        /// </summary>
        List<SqlserverFeild> SqlserverFeild;
        public void GetSqlserverTableStructure(string selectTableName)
        {
            try
            {
                connsql = new SqlConnection(connstring);
                connsql.Open();
                string queryTable = string.Format("sp_columns {0}", selectTableName);
                cmdsql = new SqlCommand(queryTable, connsql);
                var reader = cmdsql.ExecuteReader(); //④执行Command的ExecuteReader()方法  
                DataTable dt = new DataTable();
                dt.Load(reader);
                SqlserverFeild = GetModelFromDB<SqlserverFeild>(dt);
                CreateSqlserverEntity(SqlserverFeild, selectTableName);
                this.TableStructure.DataSource = dt;
                connsql.Close();
            }
            catch (Exception ex)
            {
                TableEntity.Text = ex.Message;
                throw;
            }
        }
        /// <summary>
        /// 生成mysql实体
        /// </summary>
        public void CreateSqlserverEntity(List<SqlserverFeild> field, string tableName)
        {
            string Entity = "public class " + tableName + "{\r\n";
            foreach (var item in field)
            {
                if (item.type_name == "int")
                {
                    Entity += "     public int? " + item.column_name + "{ get; set; }\r\n";
                }
                else if (item.type_name == "varchar")
                {
                    Entity += "     public string " + item.column_name + "{ get; set; }\r\n";
                }
                else if (item.type_name == "bit")
                {
                    Entity += "     public bool? " + item.column_name + "{ get; set; }\r\n";
                }
                else
                {
                    Entity += "     public string " + item.column_name + "{ get; set; }\r\n";
                }
            }
            Entity += "}";
            this.TableEntity.Text = Entity;
        }
        #endregion
        #region mysql生成实体
        /// <summary>
        /// mysql显示表
        /// </summary>
        public void MysqlShowTable()
        {
            try
            {
                conn = new MySqlConnection(connstring);
                conn.Open();
                Regex rg = new Regex("(?<=(Database))[.\\s\\S]*?(?=(;))", RegexOptions.Multiline | RegexOptions.Singleline);
                string dataTableName = rg.Match(connstring).Value;
                if (dataTableName.Contains("="))
                {
                    dataTableName = dataTableName.Replace("=", "");
                }
                string queryTable = "show tables from " + dataTableName;
                cmd = new MySqlCommand(queryTable, conn);
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Tablenames.Add(reader.GetString(0));//将表名添加到定义好的列表中来
                        this.TableName.Items.Add(reader.GetString(0));
                    }
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                TableEntity.Text = ex.Message;
                throw;
            }
        }
        /// <summary>
        /// 获取mysql表结构
        /// </summary>
        List<MySqlFeild> mySqlFeild;
        public void GetMysqlTableStructure(string selectTableName)
        {
            try
            {
                List<string> tableStructureList = new List<string>();
                conn = new MySqlConnection(connstring);
                conn.Open();
                string queryTable = string.Format("SELECT column_name,data_type FROM INFORMATION_SCHEMA.COLUMNS where table_name ='{0}' ", selectTableName);
                cmd = new MySqlCommand(queryTable, conn);
                var reader = cmd.ExecuteReader(); //④执行Command的ExecuteReader()方法  
                DataTable dt = new DataTable();
                dt.Load(reader);
                mySqlFeild = GetModelFromDB<MySqlFeild>(dt);
                CreateMysqlEntity(mySqlFeild, selectTableName);
                this.TableStructure.DataSource = dt;
                conn.Close();
            }
            catch (Exception ex)
            {
                this.TableStructure.Text = ex.Message;
                throw;
            }
        }
        /// <summary>
        /// 生成mysql实体
        /// </summary>
        /// <param name="field"></param>
        /// <param name="tableName"></param>
        public void CreateMysqlEntity(List<MySqlFeild> field, string tableName)
        {
            string Entity = "public class " + tableName + "{\r\n";
            foreach (var item in field)
            {
                if (item.data_type == "int")
                {
                    Entity += "     public int? " + item.column_name + "{ get; set; }\r\n";
                }
                else if (item.data_type.Contains("varchar"))
                {
                    Entity += "     public string " + item.column_name + "{ get; set; }\r\n";
                }
                else if (item.data_type == "bit")
                {
                    Entity += "     public bool? " + item.column_name + "{ get; set; }\r\n";
                }
                else
                {
                    Entity += "     public string " + item.column_name + "{ get; set; }\r\n";
                }
            }
            Entity += "}";
            this.TableEntity.Text = Entity;
        }
        #endregion

        #region DataTable转换成实体
        public static List<T> GetModelFromDB<T>(DataTable dt)
        {
            List<T> data = new List<T>();
            foreach (DataRow row in dt.Rows)
            {
                T item = GetItem<T>(row);
                data.Add(item);
            }
            return data;

        }
        /// <summary>
        /// 将DataRow转换成实体对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dr"></param>
        /// <returns></returns>
        private static T GetItem<T>(DataRow dr)
        {
            try
            {
                Type temp = typeof(T);
                T obj = Activator.CreateInstance<T>();
                foreach (DataColumn column in dr.Table.Columns)
                {
                    foreach (PropertyInfo pro in temp.GetProperties())
                    {
                        if (pro.Name.ToLower() == column.ColumnName.ToLower())
                        {
                            if (dr[column.ColumnName] == DBNull.Value)
                            {
                                pro.SetValue(obj, " ", null);
                                break;
                            }
                            else
                            {
                                pro.SetValue(obj, dr[column.ColumnName], null);
                                break;
                            }
                        }
                    }
                }
                return obj;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        private void Form1_Load(object sender, EventArgs e)
        {
            SqlType.Items.Insert(0, "sqlserver");
            SqlType.Items.Insert(1, "mysql");
            SqlType.SelectedIndex = 0;
        }
    }
}

